--    Author    : ChenErHao
--    Name      : DM.T_MTH_BAD_LOAN_BALANCE_INCR.HQL
--    Functions : 风险-不良贷款余额当月增量分析
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-22  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.T_MTH_BAD_LOAN_BALANCE_INCR PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#') 
SELECT 

    nvl(T1.AREA_NO_ID,9999),
    T1.ORG_ID,
    nvl(T1.CUSTOMER_TYPE,9999),
    T1.PRODUCT_CATEGORY,
    nvl(T1.LOAN_PERIOD,9999),--贷款期限,
    T1.LPIC,--企业分行业,
    T1.LOANS_ACTUALLY, -- 贷款实际投向,
    T1.ENTERPRISE_SCALE,---企业规模,
    T1.ENTERPRISE_INVESTOR,--企业出资人经济成分,
    T1.GUARANTY_STYLE,--担保方式,
    T1.LOAN_STATUS,--贷款状态,
    T1.LOAN_QUALITY,--贷款质量,
    nvl(sum(LOAN_BALANCE),0) LOAN_BALANCE,
    nvl(sum(LAST_MTH_LOAN_BALANCE),0)  LAST_MTH_LOAN_BALANCE,
    nvl(sum(LAST_YEAR_LOAN_BALANCE),0) LAST_YEAR_LOAN_BALANCE,
    nvl(sum(WEIGHT_AMT),0) WEIGHT_AMT
FROM
    (
        select 
    D1.AREA_NO_ID,
    D1.ORG_ID,
    D1.CUSTOMER_TYPE,
    D1.PRODUCT_CATEGORY,
    D1.LOAN_PERIOD,--贷款期限,
    D1.LPIC,--企业分行业,
    D1.LOANS_ACTUALLY, -- 贷款实际投向,
    D1.ENTERPRISE_SCALE,---企业规模,
    D1.ENTERPRISE_INVESTOR,--企业出资人经济成分,
    D1.GUARANTY_STYLE,--担保方式,
    D1.LOAN_STATUS,--贷款状态,
    D1.LOAN_QUALITY,--贷款质量,
    nvl(SUM(case when FISCAL_MTH_DIMNSN_ID='#V_DATA_DATE#' then D1.LOAN_BALANCE end),0) - nvl(SUM(case when FISCAL_MTH_DIMNSN_ID='#V_LAST_1M_END_DATE#' then D1.LOAN_BALANCE end),0) LOAN_BALANCE,  
    0  LAST_MTH_LOAN_BALANCE,
    0 LAST_YEAR_LOAN_BALANCE,
    nvl(SUM(case when FISCAL_MTH_DIMNSN_ID='#V_DATA_DATE#' then D1.WEIGHT_AMT end),0) - nvl(SUM(case when FISCAL_MTH_DIMNSN_ID='#V_LAST_1M_END_DATE#' then  D1.WEIGHT_AMT end),0) WEIGHT_AMT
    FROM (SELECT
            DATA_DATE              FISCAL_MTH_DIMNSN_ID,
            BELONG_AREA_CODE        AREA_NO_ID, 
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END   ORG_ID,
            CUSTOMER_TYPE         CUSTOMER_TYPE, --客户类型
            CASE WHEN C.LOAN_PRO_ID_1 IS NULL THEN '9999'
                 WHEN C.LOAN_PRO_ID_1 ='' THEN '9999'
                 ELSE C.LOAN_PRO_ID_1 END PRODUCT_CATEGORY, --贷款产品类别
            LOANS_PERIOD           LOAN_PERIOD,--贷款期限,
            NVL(LPIC_SUB,'9999') LPIC, --企业分行业
            CASE WHEN G.LOAN_ACTUALLY_ID_1 IS NULL THEN '9999'
                 WHEN G.LOAN_ACTUALLY_ID_1 ='' THEN '9999'
                 ELSE G.LOAN_ACTUALLY_ID_1 END LOANS_ACTUALLY, --行业投向
            nvl(ENTERPRISE_SCALE,'CS05') ENTERPRISE_SCALE,--企业规模
            CASE WHEN E.INVESTOR_FINICL_ID_2 IS NULL THEN '9999'
                 WHEN E.INVESTOR_FINICL_ID_2 ='' THEN '9999'
                 ELSE E.INVESTOR_FINICL_ID_2 END  ENTERPRISE_INVESTOR,--企业出资人经济成分二级编码 
            CASE WHEN F.LOAN_INSUR_ID_1 IS NULL THEN '9999'
                 WHEN F.LOAN_INSUR_ID_1 ='' THEN '9999'
                 ELSE F.LOAN_INSUR_ID_1 END   GUARANTY_STYLE,-- 贷款担保方式一级编码
            nvl(LOAN_STATUS ,'9999')           LOAN_STATUS,--贷款状态
            nvl(LOAN_QUALITY ,'9999')         LOAN_QUALITY,--贷款质量
            SUM(LOAN_BALANCE)           LOAN_BALANCE,
            SUM(LOAN_OCCURRENCE_LEVEL)  WEIGHT_AMT
        FROM
            (SELECT DATA_DATE,BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY,LPIC_SUB,LOANS_ACTUALLY,ENTERPRISE_INVESTOR,GUARANTY_STYLE,
           CUSTOMER_TYPE,LOANS_PERIOD,ENTERPRISE_SCALE,LOAN_STATUS,LOAN_QUALITY,LOAN_BALANCE,LOAN_OCCURRENCE_LEVEL
           FROM 
            EDW.LOAB WHERE
            DATA_DATE IN( '#V_DATA_DATE#','#V_LAST_1M_END_DATE#')
        AND LOAN_QUALITY IN ('FQ03','FQ04','FQ05')) A
        LEFT JOIN DMCODE.T_ORG_BIZ_LVL  B 
        ON CASE WHEN a.DATA_ORG_CODE IS NULL THEN CONCAT('DATA_ORG_CODE',RAND()) ELSE A.DATA_ORG_CODE END = B.ORG_ID  --金融机构
        LEFT JOIN DMCODE.T_LOAN_PRO_TYPE C 
        ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('PRODUCT_CATEGORY',RAND()) ELSE A.PRODUCT_CATEGORY END = C.LOAN_PRO_ID --贷款产品类别
        LEFT JOIN DMCODE.T_LOANS_ACTUALLY G 
        ON CASE WHEN a.LOANS_ACTUALLY IS NULL THEN CONCAT('LOANS_ACTUALLY',RAND()) ELSE A.LOANS_ACTUALLY END =G.LOAN_ACTUALLY_ID   --贷款行业投向
        LEFT JOIN DMCODE.T_INVESTOR_FINICL_TYPE_TRANS E 
        ON CASE WHEN a.ENTERPRISE_INVESTOR IS NULL THEN CONCAT('ENTERPRISE_INVESTOR',RAND()) ELSE A.ENTERPRISE_INVESTOR END = E.INVESTOR_FINICL_ID --企业出资人经济成分
        LEFT JOIN DMCODE.T_LOAN_INSUR_TYPE F 
        ON CASE WHEN a.GUARANTY_STYLE IS NULL THEN CONCAT('GUARANTY_STYLE',RAND()) ELSE A.GUARANTY_STYLE END =F.LOAN_INSUR_ID   --贷款担保方式
        
        GROUP BY
            DATA_DATE   ,
            BELONG_AREA_CODE , 
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END  ,
            CUSTOMER_TYPE  , 
            CASE WHEN C.LOAN_PRO_ID_1 IS NULL THEN '9999'
                 WHEN C.LOAN_PRO_ID_1 ='' THEN '9999'
                 ELSE C.LOAN_PRO_ID_1 END  ,  
            LOANS_PERIOD  , 
            LPIC_SUB ,
            CASE WHEN G.LOAN_ACTUALLY_ID_1 IS NULL THEN '9999'
                 WHEN G.LOAN_ACTUALLY_ID_1 ='' THEN '9999'
                 ELSE G.LOAN_ACTUALLY_ID_1 END,
            ENTERPRISE_SCALE ,
            CASE WHEN E.INVESTOR_FINICL_ID_2 IS NULL THEN '9999'
                 WHEN E.INVESTOR_FINICL_ID_2 ='' THEN '9999'
                 ELSE E.INVESTOR_FINICL_ID_2 END ,
            CASE WHEN F.LOAN_INSUR_ID_1 IS NULL THEN '9999'
                 WHEN F.LOAN_INSUR_ID_1 ='' THEN '9999'
                 ELSE F.LOAN_INSUR_ID_1 END ,
            LOAN_STATUS       ,
            LOAN_QUALITY )D1 
GROUP BY  
         D1.AREA_NO_ID,
         D1.ORG_ID,
         D1.CUSTOMER_TYPE,
         D1.PRODUCT_CATEGORY,
         D1.LOAN_PERIOD,
         D1.LPIC,
         D1.LOANS_ACTUALLY,
         D1.ENTERPRISE_SCALE,
         D1.ENTERPRISE_INVESTOR,
         D1.GUARANTY_STYLE,
         D1.LOAN_STATUS,
         D1.LOAN_QUALITY 
         
   union all 
   
        SELECT
            AREA_NO_ID,
            ORG_ID,
            CUSTOMER_TYPE,
            PRODUCT_CATEGORY,
            LOAN_PERIOD,--贷款期限,
            LPIC,--企业分行业,
            LOANS_ACTUALLY, --贷款实际投向,
            ENTERPRISE_SCALE,--企业规模,
            ENTERPRISE_INVESTOR,--企业出资人经济成分,
            GUARANTY_STYLE,---担保方式,
            LOAN_STATUS,--贷款状态,
            LOAN_QUALITY,--贷款质量,
            0 LOAN_BALANCE,
            case when FISCAL_MTH_DIMNSN_ID='#V_LAST_1M_END_MONTHE#' then  SUM(LOAN_BALANCE) else 0 end LAST_MTH_LOAN_BALANCE,--上月
            case when FISCAL_MTH_DIMNSN_ID='#V_LAST_12M_END_MONTH#' then  SUM(LOAN_BALANCE) else 0 end LAST_YEAR_LOAN_BALANCE,--去年同期
            0  WEIGHT_AMT
        FROM
            DM.T_MTH_BAD_LOAN_BALANCE_INCR
        WHERE
            FISCAL_MTH_DIMNSN_ID in( '#V_LAST_1M_END_MONTHE#','#V_LAST_12M_END_MONTH#')
        GROUP BY
            FISCAL_MTH_DIMNSN_ID,
            AREA_NO_ID,
            ORG_ID,
            CUSTOMER_TYPE,
            PRODUCT_CATEGORY,
            LOAN_PERIOD,
            LPIC,
            LOANS_ACTUALLY,
            ENTERPRISE_SCALE,
            ENTERPRISE_INVESTOR,
            GUARANTY_STYLE,
            LOAN_STATUS,
            LOAN_QUALITY 
     ) T1 
GROUP BY
    T1.AREA_NO_ID,
    T1.ORG_ID,
    T1.CUSTOMER_TYPE,
    T1.PRODUCT_CATEGORY,
    T1.LOAN_PERIOD,---贷款期限,
    T1.LPIC,--企业分行业,
    T1.LOANS_ACTUALLY, -- 贷款实际投向,
    T1.ENTERPRISE_SCALE,---企业规模,
    T1.ENTERPRISE_INVESTOR,---企业出资人经济成分,
    T1.GUARANTY_STYLE,--#担保方式,
    T1.LOAN_STATUS,---贷款状态,
    T1.LOAN_QUALITY
;